const db = require('@/db/db-connection')
const { multipleColumnSet, newRandomId, dateFormat, isEmpty } = require('@/utils/common.util')

class Model {
  constructor (tableName) {
    this.db = db
    this.multipleColumnSet = multipleColumnSet
    this.newRandomId = newRandomId
    this.dateFormat = dateFormat
    this.tableName = tableName
  }

  /**
   * where and 条件语句构造
   * @param {*} conditions
   * @returns
   */
  generateWhereClause (conditions = {}) {
    const equalSql = Object.entries(conditions.equal || {})
      .filter(([_k, value]) => value)
      .reduce((prev, [k, v]) => {
        v = v.replace(/%/g, '\\%').replace(/_/g, '\\_')
        if (!prev) {
          prev += `where ${k} = "${v}"`
        } else {
          prev += ` and ${k} = "${v}"`
        }
        return prev
      }, '')
    const completeSql = Object.entries(conditions.like || {})
      .filter(([_k, value]) => value)
      .reduce((prev, [k, v]) => {
        v = v.replace(/%/g, '\\%').replace(/_/g, '\\_')
        if (!prev) {
          prev += `where ${k} like "%${v}%"`
        } else {
          prev += ` and ${k} like "%${v}%"`
        }
        return prev
      }, equalSql)
    return completeSql || 'where 1 = 1'
  }

  /**
   * where or 条件语句构造
   * @param {*} conditions
   * @param {*} initSql
   */
  generateWhereClauseOR (conditions = {}, initSql) {
    const equalSql = Object.entries(conditions.equal || {})
      .filter(([_k, value]) => value)
      .reduce((prev, [k, v]) => {
        v = v.replace(/%/g, '\\%').replace(/_/g, '\\_')
        if (!prev) {
          prev += `${k} = "${v}"`
        } else {
          prev += ` or ${k} = "${v}"`
        }
        return prev
      }, '')
    const conditionsSQL = Object.entries(conditions.like || {})
      .filter(([_k, value]) => value)
      .reduce((prev, [k, v]) => {
        v = v.replace(/%/g, '\\%').replace(/_/g, '\\_')
        if (!prev) {
          prev += `${k} like "%${v}%"`
        } else {
          prev += ` or ${k} like "%${v}%"`
        }
        return prev
      }, equalSql)
    if (initSql && conditionsSQL) {
      return `${initSql} and (${conditionsSQL})`
    }
    return initSql || `where ${conditionsSQL || '1 = 1'}`
  }

  /**
   * 区间查询语句构造
   * @param {*} conditions
   * @param {*} initSql
   */
  generateBetweenClause (conditions = {}, initSql) {
    const betweenConditions = Object.entries(conditions).filter(([k, v]) => {
      return Array.isArray(v) && (!isEmpty(v[0]) || !isEmpty(v[1]))
    })

    /**
     * 根据区间值数据生成between and 语句
     * @param {*} k
     * @param {*} v
     */
    function dealwithBetweensql (k, v) {
      if (!isEmpty(v[0]) && !isEmpty(v[1])) {
        return `${k} between '${v[0]}' and '${v[1]}'`
      }
      if (!isEmpty(v[0])) {
        return `${k} > '${v[0]}'`
      }
      if (!isEmpty(v[1])) {
        return `${k} < '${v[1]}'`
      }
    }

    const conditionsSQL = betweenConditions.reduce((prev, [k, v]) => {
      if (!prev) {
        prev += dealwithBetweensql(k, v)
      } else {
        prev += ` and ${dealwithBetweensql(k, v)}`
      }
      return prev
    }, '')
    if (initSql && conditionsSQL) {
      return `${initSql} and (${conditionsSQL})`
    }
    return initSql || `where ${conditionsSQL || '1 = 1'}`
  }

  async exist (id) {
    const sql = `select ${this.tableName}.id from ${this.tableName} where id = ?`
    try {
      const data = await this.db.query(sql, [id])
      return Boolean(data[0])
    } catch (error) {
      throw new Error(error)
    }
  }

  async findOneById (id) {
    const sql = `select * from ${this.tableName} where id = ?`
    try {
      const data = await this.db.query(sql, [id])
      return data[0]
    } catch (error) {
      throw new Error(error)
    }
  }

  async findAll () {
    const sql = `select * from ${this.tableName}`
    try {
      const data = this.db.query(sql)
      return data
    } catch (error) {
      throw new Error(error)
    }
  }

  async alltotal () {
    const sql = `select count(*) as total from ${this.tableName}`
    try {
      const data = await this.db.query(sql)
      return data[0].total
    } catch (error) {
      throw new Error(error)
    }
  }

  isNull (value) {
    return value === null ||
    value === undefined ||
    value === ''
  }
}

module.exports = Model
